﻿using System;
using System.IO;
using System.Data;
using System.Configuration;
//using Oracle.DataAccess;
//using Oracle.DataAccess.Client;
using System.Data.OracleClient;
using System.Collections;
/// <summary>
/// @Oracle DB class
/// @author: Zegna chan
/// </summary>
public class OracleDB
{
    public OracleDB(string ConnectionString)
    {
        ConString = ConnectionString;
        //init();
    }
    public OracleDB()
    {
        ConString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    }
    #region 静态属性
    public static string ConString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
    protected static OracleConnection conn = new OracleConnection();
    protected static OracleCommand comm = new OracleCommand();
    #endregion



    ///打开连接
    private static void openConnection()
    {
        if (conn.State == ConnectionState.Closed)
        {
            //连接字符串

            conn.ConnectionString = ConString;
            //comm.Connection = conn;
            try
            {
                conn.Open();
                comm.Connection = conn;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }
    }

    // 关闭当前数据库连接
    private static void closeConnection()
    {
        if (conn.State == ConnectionState.Open)
            conn.Close();
        //此处已做修改
        //conn.Dispose();
        // comm.Dispose();
    }

    ///检查数据库是否可以打开
    ///

    public bool CheckOC()
    {
        try
        {
            openConnection();
            return true;
        }
        catch
        {
            return false;
        }
    }

    /// 执行Sql查询语句(无返回值)
    /// <param name="sqlstr">传入的Sql语句</param>
    /// 将static方法修改为非static方法 
    // public static void ExecuteSql(string sqlstr)
    public void ExecuteSql(string sqlstr)
    {
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
    }

    /// <summary>
    /// 执行Sql查询语句并返回第一行的第一列,返回值为object 使用时需要拆箱操作 -> Unbox
    /// </summary>
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <returns>object 返回值 </returns>
    ///   将static方法修改为非static方法 
    //public static object ExecuteScalar(string sqlstr)
    public object ExecuteScalar(string sqlstr)
    {
        object obj = new object();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            obj = comm.ExecuteScalar();

        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
        return obj;
    }

    /// 返回指定Sql语句的OracleDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
    /// 方法关闭数据库连接
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <returns>OracleDataReader对象</returns>
    /// 将static方法修改为非static方法 
    //public static OracleDataReader DataReader(string sqlstr)
    public OracleDataReader DataReader(string sqlstr)
    {
        OracleDataReader dr = null;
        try
        {
            openConnection();
            comm.CommandText = sqlstr;
            comm.CommandType = CommandType.Text;
            dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            try
            {
                dr.Close();
            }
            catch
            {
            }
        }
        finally
        {
            closeConnection();
        }
        return dr;
    }

    //dataReader 重载
    /// <summary>
    /// 返回指定Sql语句的OracleDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
    /// 方法关闭数据库连接
    /// </summary>
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <param name="dr">传入的ref DataReader 对象</param>
    /// 将static方法修改为非static方法 
    //public static void DataReader(string sqlstr, ref OracleDataReader dr)
    public void DataReader(string sqlstr, ref OracleDataReader dr)
    {
        try
        {
            openConnection();
            comm.CommandText = sqlstr;
            comm.CommandType = CommandType.Text;
            dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            try
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            catch
            {
            }
            finally
            {
            }
        }
        finally
        {
            closeConnection();
        }
    }

    /// <summary>
    /// 返回指定Sql语句的DataSet
    /// </summary>
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <returns>DataSet</returns>
    /// 将static方法修改为非static方法  
    //public static DataSet DataSet(string sqlstr)
    public DataSet DataSet(string sqlstr)
    {
        DataSet ds = new DataSet();
        OracleDataAdapter da = new OracleDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
        return ds;
    }

    /// <summary>
    /// 返回指定Sql语句的DataSet
    /// </summary>
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <param name="ds">传入的引用DataSet对象</param>
    /// 将static方法修改为非static方法 
    //public static void DataSet(string sqlstr, ref DataSet ds)
    public void DataSet(string sqlstr, ref DataSet ds)
    {
        OracleDataAdapter da = new OracleDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
    }
    /// <summary>
    /// 返回指定Sql语句的DataTable
    /// </summary>
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <returns>DataTable</returns>
    /// 将static方法修改为非static方法 
    //public static DataTable DataTable(string sqlstr)
    public DataTable DataTable(string sqlstr)
    {
        OracleDataAdapter da = new OracleDataAdapter();
        DataTable Datatable = new DataTable();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(Datatable);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
        return Datatable;
    }

    /// <summary>
    /// 执行指定Sql语句,同时给传入DataTable进行赋值 
    /// </summary>
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <param name="dt">ref DataTable dt </param>
    /// 将static方法修改为非static方法 
    //public static void DataTable(string sqlstr, ref DataTable dt)
    public void DataTable(string sqlstr, ref DataTable dt)
    {
        OracleDataAdapter da = new OracleDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(dt);
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
    }

    /// <summary>
    /// 返回指定Sql语句的DataView
    /// </summary>
    /// <param name="sqlstr">传入的Sql语句</param>
    /// <returns>DataView</returns>
    /// 将static方法修改为非static方法 
    //public static DataView DataView(string sqlstr)
    public DataView DataView(string sqlstr)
    {
        OracleDataAdapter da = new OracleDataAdapter();
        DataView dv = new DataView();
        DataSet ds = new DataSet();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);
            dv = ds.Tables[0].DefaultView;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
        return dv;
    }
    public void ExecuteSql(string sqlstr, ref int infection)
    {
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            infection = comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
    }



    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>		
    public void ExecuteSqlTran(ArrayList SQLStringList)
    {
        using (OracleConnection conn = new OracleConnection(ConString))
        {
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            OracleTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n].ToString();
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
#if DEBUGLOG
                            LogHelper.WriteSQLToLog(strsql);
#endif
                        cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
            }
            catch (System.Data.OracleClient.OracleException E)
            {
#if DEBUGLOG
                    LogHelper.WriteErrToLog(E.Message + E.StackTrace);
#endif
                tx.Rollback();
                throw new Exception(E.Message);
            }
        }
    }


    /// <summary>
    /// orcale　存储过程调用函数（存储过程名，其所传参数，outnum后均返回值序号）aiks　20071029
    /// </summary>
    /// <param name="storedProcName"></param>
    /// <param name="parameters"></param>
    /// <param name="outnum"></param>

    public void RunProcedure(string storedProcName, OracleParameter[] parameters, int outnum)
    {
        int Parnum = 0;
        try
        {
            comm.Parameters.Clear();
            openConnection();
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = storedProcName;
            foreach (OracleParameter parameter in parameters)
            {
                comm.Parameters.Add(parameter);
                if (Parnum >= outnum - 1)
                    parameters[Parnum].Direction = ParameterDirection.Output;
                else
                    parameters[Parnum++].Direction = ParameterDirection.Input;

            }
            comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            comm.Parameters.Clear();
            closeConnection();
        }
    }

    public DataTable TableProcedure(string storedProcName, OracleParameter[] parameters, int outnum)
    {
        int Parnum = 0;
        try
        {
            comm.Parameters.Clear();
            openConnection();
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = storedProcName;
            foreach (OracleParameter parameter in parameters)
            {
                comm.Parameters.Add(parameter);
                if (Parnum >= outnum - 1)
                    parameters[Parnum].Direction = ParameterDirection.Output;
                else
                    parameters[Parnum++].Direction = ParameterDirection.Input;

            }
            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = comm;
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            comm.Parameters.Clear();
            closeConnection();
        }
    }

    //以下方法用于事务

    public string getConnectionString()
    {
        return ConString;
    }

    public string executeScalar(OracleCommand olecmd, string sql)
    {
        //olecmd = new OracleCommand();
        olecmd.CommandType = CommandType.Text;
        olecmd.CommandText = sql;
        return olecmd.ExecuteScalar().ToString();
    }
    public void executeNonQuery(OracleCommand olecmd, string sql)
    {
        // olecmd = new OracleCommand();
        olecmd.CommandType = CommandType.Text;
        olecmd.CommandText = sql;
        olecmd.ExecuteNonQuery();
    }

    public DataSet executeDataSet(OracleCommand olecmd, string sql)
    {
        DataSet ds = new DataSet();
        OracleDataAdapter da = new OracleDataAdapter();
        olecmd.CommandType = CommandType.Text;
        olecmd.CommandText = sql;
        da.SelectCommand = olecmd;
        da.Fill(ds);

        return ds;
    }

}